﻿// Project: Salient
// http://salient.codeplex.com
// 
// Copyright 2010, Sky Sanders <sky at skysanders.net>
// Dual licensed under the MIT or GPL Version 2 licenses.
// http://salient.codeplex.com/license
// 
// Date: April 24 2010 

#region

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
#endregion

namespace Salient.SqlServer.Management
{

    ///<summary>
    /// A class that can run Sql DDL scripts by splitting them into individual batches on GO
    ///</summary>
    public class SqlCmdRunner : Salient.Data.SqlClient.SqlConnectionStringBuilder
    {
        private static readonly Regex ScriptSplit = new Regex(@"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
        ///<summary>
        ///</summary>
        ///<param name="script"></param>
        ///<returns></returns>
        public int[] ExecuteScript(StringCollection script)
        {
            return ExecuteScript(script, false);
        }

        ///<summary>
        ///</summary>
        ///<param name="scripts"></param>
        ///<param name="continueOnError"></param>
        ///<returns></returns>
        ///<exception cref="Exception"></exception>
        public int[] ExecuteScript(StringCollection scripts, bool continueOnError)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                List<int>  result = new List<int>();

                foreach (string script in scripts)
                {
                    int count = 0;
                    string[] items = ScriptSplit.Split(script);
                    var cmd = conn.CreateCommand();

                    foreach (string item in items)
                    {
                        if (!string.IsNullOrEmpty(item.Trim()))
                        {
                            cmd.CommandText = item;

                            try
                            {
                                count += cmd.ExecuteNonQuery();
                            }
                            catch
                            {
                                if (!continueOnError)
                                {
                                    throw;
                                }
                            }
                        }
                    }
                    result.Add(count);
                }
                return result.ToArray();
            }
        }
    }

    ///// <summary>
    ///// TODO: utilize the custom Salient.Data.SqlClient.SqlConnectionStringBuilder to redesign this
    ///// to split sql scripts on 'GO' and execute them via ADO to eliminate the dependency on SMO
    ///// </summary>
    //public class SqlCmdRunner
    //{
    //    private SqlConnectionStringBuilder _csBuilder = new SqlConnectionStringBuilder();

    //    #region Connection String Properties

    //    ///<summary>
    //    ///</summary>
    //    public string InitialCatalog
    //    {
    //        get { return _csBuilder.InitialCatalog; }
    //        set { _csBuilder.InitialCatalog = value; }
    //    }

    //    ///<summary>
    //    ///</summary>
    //    public string DataSource
    //    {
    //        get { return _csBuilder.DataSource; }
    //        set { _csBuilder.DataSource = value; }
    //    }

    //    ///<summary>
    //    ///</summary>
    //    public string ConnectionString
    //    {
    //        get { return _csBuilder.ConnectionString; }
    //        set { _csBuilder = new SqlConnectionStringBuilder(value); }
    //    }

    //    ///<summary>
    //    ///</summary>
    //    public bool IntegratedSecurity
    //    {
    //        get { return _csBuilder.IntegratedSecurity; }
    //        set { _csBuilder.IntegratedSecurity = value; }
    //    }

    //    ///<summary>
    //    ///</summary>
    //    public string UserID
    //    {
    //        get { return _csBuilder.UserID; }
    //        set { _csBuilder.UserID = value; }
    //    }

    //    ///<summary>
    //    ///</summary>
    //    public string Password
    //    {
    //        get { return _csBuilder.Password; }
    //        set { _csBuilder.Password = value; }
    //    }

    //    #endregion

    //    ///<summary>
    //    ///</summary>
    //    ///<param name="script"></param>
    //    ///<returns></returns>
    //    public int[] ExecuteScript(StringCollection script)
    //    {
    //        return ExecuteScript(script, ExecutionTypes.Default);
    //    }

    //    ///<summary>
    //    ///</summary>
    //    ///<param name="script"></param>
    //    ///<param name="executionTypes"></param>
    //    ///<returns></returns>
    //    ///<exception cref="Exception"></exception>
    //    public int[] ExecuteScript(StringCollection script, ExecutionTypes executionTypes)
    //    {
    //        using (SqlConnection conn = new SqlConnection(ConnectionString))
    //        {
    //            ServerConnection serverConn = new ServerConnection(conn);
    //            Server server = new Server(serverConn);

    //            // ExecutionFailureException
                
    //            int[] result;
    //            try
    //            {
    //                result = server.ConnectionContext.ExecuteNonQuery(script, executionTypes);
    //            }
    //            catch (ExecutionFailureException ex)
    //            {
    //                throw ex.InnerException;
    //            }
    //            return result;
    //        }
    //    }
    //}
}